0% completed
Problem Statement
Table: World
Each row in this table represents a city, detailing its name, the country it's in, its area in square kilometers, and its population.
+-----------------+---------+
| Column Name | Type |
+-----------------+---------+
| city_name | varchar |
| country | varchar |
| area | int | -- in square kilometers
| population | int |
+-----------------+---------+
city_name is the primary key for this table.
This table contains information about the city name, the country it is in, its area in square kilometers, and its population.
Develop a solution to identify the names of major cities that either have an area greater than 3000 square kilometers or a population exceeding 5 million people. The output should be sorted in any order.
Example
Input:
World table: +-----------+---------+-------+------------+ | city_name | country | area | population | +-----------+---------+-------+------------+ | Tokyo | Japan | 6223 | 13929286 | | Delhi | India | 1484 | 11034555 | | Shanghai | China | 6341 | 24256800 | | New York | USA | 783 | 8336817 | | Paris | France | 105 | 2148271 | +-----------+---------+-------+------------+
Expected Output:
+-----------+ | city_name | +-----------+ | Tokyo | | Delhi | | Shanghai | | New York | +-----------+
Tokyo, Shanghai, and New York are identified as major cities either due to their large area or high population.
Try it YourSelf
World =
Solution
To find major cities based on their area or population, we'll execute SQL queries to filter and retrieve the needed information.
- Select City Names: Begin with a
SELECTstatement to fetch thecity_namefrom theWorldtable. - Apply Conditions: Use a
WHEREclause to filter cities either with anarea > 3000orpopulation > 5000000.
SQL Query
Here's the SQL query that addresses this:
SELECT city_name FROM World WHERE area > 3000 OR population > 5000000;
Step by Step Approach
Step 1: Select City Names
Start by selecting the city_name from the World table, which will form the core of our output.
SELECT city_name FROM World
Output After Step 1:
+-----------+ | city_name | +-----------+ | Tokyo | | Delhi | | Shanghai | | New York | | Paris | +-----------+
Step 2: Apply Conditions
Refine the selection by applying conditions to include only cities that are either significantly large in area (area > 3000) or have a substantial population (population > 5000000).
SELECT city_name FROM World WHERE area > 3000 OR population > 5000000
Final Output:
+-----------+ | city_name | +-----------+ | Tokyo | | Delhi | | Shanghai | | New York | +-----------+
For your reference, here is the complete final query:
SELECT city_name FROM World WHERE area > 3000 OR population > 5000000
On this page
Problem Statement
Example
Try it YourSelf
Solution
SQL Query
Step by Step Approach